﻿using System;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Text;
using System.Web;
using Framework;

namespace Enterprise
{
    public class ProductCategoryService : BaseService
    {
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public Table GetTable()
        {
            var table = new Table
            {
                Rows = DbHelperSqLite.Query("select * from tbproductcategory order by cosort desc").Tables["ds"].Rows,
                Total = DbHelperSqLite.GetSingle("select count(*) from tbproductcategory").SafeToInt()
            };
            return table;
        }
        /// <summary>
        /// 根据ID获取一条记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetById(int id)
        {
            var parameters = new[] {
                                     new SQLiteParameter("@id", DbType.Int32)
                                   };
            parameters[0].Value = id;
            return DbHelperSqLite.Query("select A.CoName,A.CoNormalImage,A.CoSmallImage,A.CoBigImage,A.CoNameEn,A.CoproductCategoryId,A.CoSort,B.CoName as CoParentName  from tbproductcategory as A left join tbproductcategory as B on A.CoParentId=B.CoproductCategoryId where A.CoproductCategoryId=@id", parameters).Tables["ds"].Rows[0];
        }

        /// <summary>
        /// 添加产品分类
        /// </summary>
        /// <param name="name">分类名称</param>
        /// <param name="nameEn"> </param>
        /// <param name="parentId">父级ID</param>
        /// <param name="sort">排序</param>
        /// <param name="filedata"> </param>
        public int Add(string name, string nameEn, int parentId, int sort, HttpPostedFileBase filedata = null)
        {
            Check.Argument.IsNotOutOfLength(name, 2, 50, "分类名称");
            Check.Argument.IsNotOutOfLength(nameEn, 2, 50, "分类名称(En)");
            var fileName = string.Empty;
            var thumFileName = string.Empty;
            if (filedata != null && filedata.ContentLength > 0)
            {
                var uploadHelper = new UploadHelper
                                       {
                                           PostedFile = filedata,
                                           RelativePath = ProductCategoryCategoryConfig.RelativePath,
                                           IsAdaptive = ProductCategoryCategoryConfig.IsAdaptive,
                                           IsImage = true,
                                           IsGenerateThumbnail = ProductCategoryCategoryConfig.IsGenerateThumbnail,
                                           FileMaxLength = ProductCategoryCategoryConfig.ImageSize,
                                           ThumbnailHeight = ProductCategoryCategoryConfig.ThumbnailHeight,
                                           ThumbnailWidth = ProductCategoryCategoryConfig.ThumbnailWidth,
                                           ThumbnailPrefix = ProductCategoryCategoryConfig.ThumbnailPrefix
                                       };
                uploadHelper.Save();
                fileName = uploadHelper.FileName;
                thumFileName = uploadHelper.ThumbnailFileName;
            }
            var strSql = new StringBuilder();
            strSql.Append("insert into tbproductcategory(");
            strSql.Append("CoName,CoNameEn,CoCategoryPath,CoParentId,CoDepth,CoType,CoLanguage,CoSort,CoNormalImage,CoSmallImage,CoBigImage");
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append("@CoName,@CoNameEn,@CoCategoryPath,@CoParentId,@CoDepth,@CoType,@CoLanguage,@CoSort,@CoNormalImage,@CoSmallImage,@CoBigImage");
            strSql.Append(");select last_insert_rowid() from tbproductcategory");
            SQLiteParameter[] parameters =
                {
                    new SQLiteParameter("@CoName", DbType.String, 255),
                    new SQLiteParameter("@CoCategoryPath", DbType.String),
                    new SQLiteParameter("@CoParentId", DbType.Int32),
                    new SQLiteParameter("@CoDepth", DbType.Int32),
                    new SQLiteParameter("@CoType", DbType.Int32),
                    new SQLiteParameter("@CoLanguage", DbType.String, 10),
                    new SQLiteParameter("@CoSort", DbType.Int32),
                    new SQLiteParameter("@CoNameEn", DbType.String, 255),
                    new SQLiteParameter("@CoNormalImage", DbType.String, 255),
                    new SQLiteParameter("@CoSmallImage", DbType.String, 255),
                    new SQLiteParameter("@CoBigImage", DbType.String, 255)
                };
            parameters[0].Value = name;
            var parentParam = new[] { new SQLiteParameter("@id", DbType.Int32) };
            parentParam[0].Value = parentId;
            using (var sdr = DbHelperSqLite.ExecuteReader("select * from tbproductcategory where CoproductCategoryId=@id", parentParam))
            {

                if (sdr.Read())
                {

                    parameters[1].Value = sdr["CoCategoryPath"].SafeToString() + parentId + ",";
                    parameters[2].Value = parentId;
                    parameters[3].Value = sdr["CoDepth"].SafeToInt() + 1;

                }
                else
                {
                    parameters[1].Value = "";
                    parameters[2].Value = DBNull.Value;
                    parameters[3].Value = 1;
                }
            }
            parameters[4].Value = 0;
            parameters[5].Value = DBNull.Value;
            parameters[6].Value = sort;
            parameters[7].Value = nameEn;
            parameters[8].Value = fileName;
            parameters[9].Value = thumFileName;
            parameters[10].Value = fileName;
            var obj = DbHelperSqLite.GetSingle(strSql.ToString(), parameters).SafeToInt32();
            return obj;

        }


        public void Edit(int id, string name, string nameEn, int sort, HttpPostedFileBase filedata = null)
        {
            Check.Argument.IsNotOutOfLength(name, 2, 50, "分类名称");
            Check.Argument.IsNotOutOfLength(nameEn, 2, 50, "分类名称(En)");
            var fileName = string.Empty;
            var thumFileName = string.Empty;
            if (filedata != null && filedata.ContentLength > 0)
            {
                var uploadHelper = new UploadHelper
                {
                    PostedFile = filedata,
                    RelativePath = ProductCategoryCategoryConfig.RelativePath,
                    IsAdaptive = ProductCategoryCategoryConfig.IsAdaptive,
                    IsImage = true,
                    IsGenerateThumbnail = ProductCategoryCategoryConfig.IsGenerateThumbnail,
                    FileMaxLength = ProductCategoryCategoryConfig.ImageSize,
                    ThumbnailHeight = ProductCategoryCategoryConfig.ThumbnailHeight,
                    ThumbnailWidth = ProductCategoryCategoryConfig.ThumbnailWidth,
                    ThumbnailPrefix = ProductCategoryCategoryConfig.ThumbnailPrefix
                };
                uploadHelper.Save();
                fileName = uploadHelper.FileName;
                thumFileName = uploadHelper.ThumbnailFileName;
            }
            var strSql = new StringBuilder();
            strSql.Append("update tbproductcategory set ");
            strSql.Append(" CoName=@CoName,");
            strSql.Append(" CoNameEn=@CoNameEn,");
            strSql.Append(" CoNormalImage=@CoNormalImage,");
            strSql.Append(" CoSmallImage=@CoSmallImage,");
            strSql.Append(" CoBigImage=@CoBigImage,");
            strSql.Append(" CoSort=@CoSort ");
            strSql.Append(" where coproductcategoryid=@id ");
            SQLiteParameter[] parameters =
                {
                    new SQLiteParameter("@id", DbType.Int32),
                    new SQLiteParameter("@CoName", DbType.String, 255),
                    new SQLiteParameter("@CoSort", DbType.Int32),
                    new SQLiteParameter("@CoNameEn", DbType.String, 255),
                    new SQLiteParameter("@CoNormalImage", DbType.String, 255),
                    new SQLiteParameter("@CoSmallImage", DbType.String, 255),
                    new SQLiteParameter("@CoBigImage", DbType.String, 255)
                };
            parameters[0].Value = id;
            parameters[1].Value = name;
            parameters[2].Value = sort;
            parameters[3].Value = nameEn;
            if (filedata != null)
            {
                parameters[4].Value = fileName;
                parameters[5].Value = thumFileName;
                parameters[6].Value = fileName;
            }
            var path = HttpContext.Current.Server.MapPath(ProductCategoryCategoryConfig.RelativePath);
            var sbtwo = new StringBuilder();
            sbtwo.Append("select * from tbproductcategory where coproductcategoryid=@id");
            var parametertwo = new[]
                                {
                                    new SQLiteParameter("@id", DbType.Int32)
                                };
            parametertwo[0].Value = id;
            var ds = DbHelperSqLite.Query(sbtwo.ToString(), parametertwo);
            foreach (DataRow item in ds.Tables["ds"].Rows)
            {
                FileHelper.DeleteSub(Path.Combine(path, item["CoNormalImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoSmallImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoBigImage"].SafeToString()));
            }
            var obj = DbHelperSqLite.ExecuteSql(strSql.ToString(), parameters).SafeToInt();
            if (!(obj > 0))
            {
                throw new Exception("记录不存在！");
            }
        }

        public void Delete(int id)
        {
            var productCategorySql = new StringBuilder();
            productCategorySql.Append("select count(*) from tbproductcategory  ");
            productCategorySql.Append(" where coproductcategoryid=@id ");
            productCategorySql.Append(" and coissystem=1");
            SQLiteParameter[] productCategoryParameters =
                {
                    new SQLiteParameter("@id", DbType.Int32)
                };
            productCategoryParameters[0].Value = id;
            var syscount = DbHelperSqLite.GetSingle(productCategorySql.ToString(), productCategoryParameters).SafeToInt();
            if (syscount > 0)
            {
                throw new Exception("系统项不能删除！");
            }

            var strchildSql = new StringBuilder();
            strchildSql.Append("select count(*) from tbproductcategory  ");
            strchildSql.Append(" where coparentid=@id ");
            SQLiteParameter[] childParameters =
                {
                    new SQLiteParameter("@id", DbType.Int32)
                };
            childParameters[0].Value = id;
            var childcount = DbHelperSqLite.GetSingle(strchildSql.ToString(), childParameters).SafeToInt();
            if (childcount > 0)
            {
                throw new Exception("该分类存在子类，请先删除子类！");
            }
            var strProductSql = new StringBuilder();
            strProductSql.Append("select count(*) from tbproduct  ");
            strProductSql.Append(" where coproductcategoryid=@id ");
            SQLiteParameter[] productParameters =
                {
                    new SQLiteParameter("@id", DbType.Int32)
                };
            productParameters[0].Value = id;
            var count = DbHelperSqLite.GetSingle(strProductSql.ToString(), productParameters).SafeToInt();
            if (count > 0)
            {
                throw new Exception("该分类下存在产品，请先删除产品记录！");
            }
            var path = HttpContext.Current.Server.MapPath(ProductCategoryCategoryConfig.RelativePath);
            var sbtwo = new StringBuilder();
            sbtwo.Append("select * from tbproductcategory where coproductcategoryid=@id");
            var parametertwo = new[]
                                {
                                    new SQLiteParameter("@id", DbType.Int32)
                                };
            parametertwo[0].Value = id;
            var ds = DbHelperSqLite.Query(sbtwo.ToString(), parametertwo);
            foreach (DataRow item in ds.Tables["ds"].Rows)
            {
                FileHelper.DeleteSub(Path.Combine(path, item["CoNormalImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoSmallImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoBigImage"].SafeToString()));
            }
            var strSql = new StringBuilder();
            strSql.Append("delete from tbproductcategory  ");
            strSql.Append(" where coproductcategoryid=@id ");
            SQLiteParameter[] parameters =
                {
                    new SQLiteParameter("@id", DbType.Int32)
                };
            parameters[0].Value = id;
            var obj = DbHelperSqLite.ExecuteSql(strSql.ToString(), parameters).SafeToInt();
            if (!(obj > 0))
            {
                throw new Exception("记录不存在！");
            }
        }

    }
}
